package springBootweb.com.dome.lession1.excle;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 
 * 测试生成Excel文档
 * 
 * @author Administrator https://www.cnblogs.com/3primarycolor/p/5668619.html
 *         https://www.journaldev.com/2562/apache-poi-tutorial#apache-poi-example-8211-read-excel-file
 *         http://www.cnblogs.com/fqfanqi/p/6172223.html
 */

public class TestExcle {

	/**
	 * Excel文档的构成 在工作簿(WorkBook)里面包含了工作表(Sheet) 在工作表里面包含了行(Row) 行里面包含了单元格(Cell)
	 * 创建一个工作簿的基本步骤 第一步 创建一个 工作簿 第二步 创建一个 工作表 第三步 创建一行 第四步 创建单元格 第五步 写数据 第六步
	 * 将内存中生成的workbook写到文件中 然后释放资源 E:\work\Test\springBootweb\src\main\resources
	 */

	private static final String FILE_PATH = "F:\\temp";
	private static final String STYLE_FILE_PATH = new File("").getAbsolutePath() + "\\src\\main\\resources\\user.xls";

	public static void createExcelOfUsers() throws Exception {
		Workbook wb = new HSSFWorkbook();

		// 创建工作表
		Sheet sheet = wb.createSheet("用户信息");

		Object[][] data = new Object[][] { { 1, "zhansgan", 20, "zhangsan@zhangsan.com" },
				{ 2, "zhansgan1", 30, "zhangsan1@zhangsan.com" }, { 3, "zhansgan2", 40, "zhangsan2@zhangsan.com" },
				{ 4, "zhansgan3", 20, "zhangsan3@zhangsan.com" }, { 5, "zhansgan4", 43, "zhangsan4@zhangsan.com" },
				{ 6, "zhansgan5", 24, "zhangsan5@zhangsan.com" }, { 7, "zhansgan6", 55, "zhangsan6@zhangsan.com" },
				{ 8, "zhansgan7", 12, "zhangsan7@zhangsan.com" }, };

		// 显示标题
		Row title_row = sheet.createRow(0);
		title_row.setHeight((short) (40 * 20));

		Cell title_cell = title_row.createCell(0);

		String headers[] = new String[] { "ID", "用户名", "年龄", "邮箱" };

		Row header_row = sheet.createRow(1);
		header_row.setHeight((short) (20 * 24));

		// 创建单元格的 显示样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER); // 水平方向上的对其方式
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直方向上的对其方式

		title_cell.setCellStyle(style);
		title_cell.setCellValue("用户详细信息");

		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));

		for (int i = 0; i < headers.length; i++) {
			// 设置列宽 基数为256
			sheet.setColumnWidth(i, 30 * 256);
			Cell cell = header_row.createCell(i);
			// 应用样式到 单元格上
			cell.setCellStyle(style);
			cell.setCellValue(headers[i]);
		}

		for (int i = 0; i < data.length; i++) {

			Row row = sheet.createRow(i + 2);
			row.setHeight((short) (20 * 20));
			// 设置行高基数为20
			for (int j = 0; j < data[i].length; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(data[i][j].toString());
			}

		}
		FileOutputStream fileOut = new FileOutputStream("F:\\temp\\users.xls");
		wb.write(fileOut);
		fileOut.close();
	}

	public static void createExcelReadFile() {
		InputStream in = null;
		Workbook wb = new HSSFWorkbook();
		Workbook outWb = new HSSFWorkbook();
		try {
			in = new FileInputStream(STYLE_FILE_PATH);
			String fileType = STYLE_FILE_PATH.substring(STYLE_FILE_PATH.lastIndexOf("."));
			if (".xls".equals(fileType)) {
				wb = new HSSFWorkbook(in);
			} else if (".xlsx".equals(fileType)) {
				wb = new HSSFWorkbook(in);
			} else {
				wb = null;
			}
			// 怎么获得sheet列表的
			// 首行样式：
			Sheet sheet = wb.getSheetAt(1);
			Row row = sheet.getRow(1);
			System.out.println(row);
			/*
			 * System.out.println(firstcolumnStyle); sheet.setDefaultColumnStyle(1,
			 * firstcolumnStyle);
			 */
			sheet.setDefaultColumnWidth(sheet.getColumnWidth(1));
			sheet.setDefaultRowHeight(sheet.getDefaultRowHeight());
			FileOutputStream fileOut = new FileOutputStream("F:\\temp\\users.xls");
			outWb.write(fileOut);
			in.close();
			fileOut.close();
			System.out.println(sheet.getFirstRowNum());

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			/// System.out.println(e.getMessage() + ">>" + "张不到这个文件");
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Map<Integer, Map<Integer, String>> readExcleStyle() {

		return null;
	}

	public static void main(String[] args) throws Exception {
		/*
		 * createExcelOfUsers(); readExcleStyle();
		 */
		createExcelReadFile();
		System.out.println();
	}

}